package com.yuqq;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.Duration;
import java.time.Instant;
import java.util.*;

public class Good2 {

    public static void main(String[] args) {
        String inputPath = "src/main/resources/input2.xlsx";
        String outputPath = "src/main/resources/output87.xlsx";

        System.out.println("开始执行 Excel 去重程序（第一列重复时，仅删除第二列无值且与有值行重复的行）");
        System.out.println("输入文件: " + inputPath);
        System.out.println("输出文件: " + outputPath);

        Instant startTime = Instant.now();

        try {
            Workbook workbook = new XSSFWorkbook(new FileInputStream(new File(inputPath)));
            Sheet sheet = workbook.getSheetAt(0);
            int totalRows = sheet.getLastRowNum() + 1;
            System.out.println("读取工作表，总行数: " + totalRows);

            if (totalRows == 0) {
                System.out.println("⚠️ 工作表为空，无需处理");
                return;
            }

            // 分组存储：第一列值 -> 第二列有值的行号列表 + 第二列无值的行号列表
            Map<String, GroupInfo> groupMap = new HashMap<>();

            ProgressTracker progressTracker = new ProgressTracker(totalRows);
            System.out.println("开始分析第一列和第二列内容...");

            for (int rowIndex = 0; rowIndex < totalRows; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }

                // 获取第一列值（分组键）
                Cell firstCell = row.getCell(0);
                String rowKey = getCellValue(firstCell);

                // 获取第二列是否有值
                boolean hasSecondValue = false;
                Cell secondCell = row.getCell(1);
                if (secondCell != null && secondCell.getCellType() != CellType.BLANK) {
                    hasSecondValue = true;
                }

                // 初始化分组信息
                groupMap.putIfAbsent(rowKey, new GroupInfo());
                GroupInfo group = groupMap.get(rowKey);

                if (hasSecondValue) {
                    group.validRows.add(rowIndex); // 第二列有值的行号
                } else {
                    group.invalidRows.add(rowIndex); // 第二列无值的行号
                }

                progressTracker.update(rowIndex);
            }

            System.out.println("\n行内容分析完成，共识别出 " + groupMap.size() + " 个不同的第一列值组");

            // 识别需要删除的行：第二列无值且第一列与有值行重复的行
            List<Integer> deleteRows = new ArrayList<>();
            System.out.println("开始识别需要删除的行...");

            for (Map.Entry<String, GroupInfo> entry : groupMap.entrySet()) {
                GroupInfo group = entry.getValue();

                // 如果该组有第二列有值的行，则删除第二列无值且第一列重复的行
                if (!group.validRows.isEmpty()) {
                    deleteRows.addAll(group.invalidRows);
                }
                // 如果该组没有第二列有值的行，则保留第二列无值的行（无需删除）
            }

            // 去重（避免重复标记删除）
            Set<Integer> deleteSet = new HashSet<>(deleteRows);
            System.out.println("需要删除的行数: " + deleteSet.size());

            // 收集需要保留的行：所有行 - 删除行
            List<Row> keptRows = new ArrayList<>();
            System.out.println("开始收集保留行...");

            ProgressTracker collectTracker = new ProgressTracker(totalRows);
            for (int rowIndex = 0; rowIndex < totalRows; rowIndex++) {
                if (!deleteSet.contains(rowIndex)) {
                    Row row = sheet.getRow(rowIndex);
                    if (row != null) {
                        keptRows.add(row);
                    }
                }
                collectTracker.update(rowIndex);
            }

            System.out.println("\n保留行收集完成，共 " + keptRows.size() + " 行");

            if (keptRows.isEmpty()) {
                System.err.println("没有保留任何行，至少需要保留表头！");
                return;
            }

            // 创建新工作表并写入保留行
            Sheet newSheet = workbook.createSheet("Sheet5");
            for (int i = 0; i < keptRows.size(); i++) {
                Row sourceRow = keptRows.get(i);
                Row newRow = newSheet.createRow(i);
                copyRowContent(sourceRow, newRow);
            }

            // 删除原工作表
            workbook.removeSheetAt(0);

            System.out.println("开始写入结果文件...");
            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                workbook.write(fos);
            }

            Instant endTime = Instant.now();
            long duration = Duration.between(startTime, endTime).getSeconds();

            System.out.println("✅ 去重完成，结果已输出到: " + outputPath);
            System.out.println("⏱ 总耗时: " + formatDuration(duration));

        } catch (IOException e) {
            System.err.println("处理过程中发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 复制行内容（值、样式、公式等）
    private static void copyRowContent(Row sourceRow, Row targetRow) {
        int lastCellNum = sourceRow.getLastCellNum();
        for (int cellIndex = 0; cellIndex < lastCellNum; cellIndex++) {
            Cell sourceCell = sourceRow.getCell(cellIndex);
            Cell targetCell = targetRow.createCell(cellIndex);

            if (sourceCell == null) {
                targetCell.setCellType(CellType.BLANK);
                continue;
            }

            // 复制单元格值
            switch (sourceCell.getCellType()) {
                case STRING:
                    targetCell.setCellValue(sourceCell.getStringCellValue());
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(sourceCell)) {
                        targetCell.setCellValue(sourceCell.getDateCellValue());
                    } else {
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    targetCell.setCellValue(sourceCell.getBooleanCellValue());
                    break;
                case FORMULA:
                    targetCell.setCellFormula(sourceCell.getCellFormula());
                    FormulaEvaluator evaluator = sourceRow.getSheet().getWorkbook()
                            .getCreationHelper().createFormulaEvaluator();
                    evaluator.evaluate(targetCell);
                    break;
                default:
                    targetCell.setCellType(CellType.BLANK);
            }

            // 复制单元格样式
            CellStyle sourceStyle = sourceCell.getCellStyle();
            if (sourceStyle != null) {
                Workbook workbook = targetRow.getSheet().getWorkbook();
                CellStyle newStyle = workbook.createCellStyle();
                newStyle.cloneStyleFrom(sourceStyle);
                targetCell.setCellStyle(newStyle);
            }
        }
    }

    // 获取单元格值
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return DateUtil.isCellDateFormatted(cell)
                        ? cell.getDateCellValue().toString()
                        : String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    // 进度跟踪器
    static class ProgressTracker {
        private final int total;
        private final long startTime;
        private int current;

        public ProgressTracker(int total) {
            this.total = total;
            this.startTime = System.currentTimeMillis();
            this.current = 0;
        }

        public void update(int current) {
            this.current = current;
            printProgress();
        }

        private void printProgress() {
            int percent = (int) ((current / (double) total) * 100);
            long elapsedTime = System.currentTimeMillis() - startTime;
            long estimatedTotalTime = (long) (elapsedTime / ((current + 1) / (double) total));
            long remainingTime = estimatedTotalTime - elapsedTime;

            String elapsedStr = formatTime(elapsedTime);
            String remainingStr = formatTime(remainingTime);

            System.out.print("\r进度: " + percent + "% (" + current + "/" + total + ") " +
                    "| 已用: " + elapsedStr + " | 预计剩余: " + remainingStr);
        }

        private String formatTime(long millis) {
            long seconds = millis / 1000;
            long minutes = seconds / 60;
            long hours = minutes / 60;

            seconds = seconds % 60;
            minutes = minutes % 60;

            if (hours > 0) {
                return String.format("%02d:%02d:%02d", hours, minutes, seconds);
            } else if (minutes > 0) {
                return String.format("%02d:%02d", minutes, seconds);
            } else {
                return seconds + "s";
            }
        }
    }

    // 格式化持续时间
    private static String formatDuration(long seconds) {
        if (seconds < 60) {
            return seconds + "秒";
        } else if (seconds < 3600) {
            return String.format("%d分%d秒", seconds / 60, seconds % 60);
        } else {
            return String.format("%d时%d分%d秒", seconds / 3600, (seconds % 3600) / 60, seconds % 60);
        }
    }

    // 分组信息辅助类：记录第二列有值和无值的行号
    static class GroupInfo {
        List<Integer> validRows = new ArrayList<>(); // 第二列有值的行号
        List<Integer> invalidRows = new ArrayList<>(); // 第二列无值的行号
    }
}